# !/usr/bin/env python
# -*- encoding: utf-8 -*-
"""
@Author  :   {Richard Li}
@License :   (C) Copyright 2013-2017, {BUPT}
@Contact :   {543306408@qq.com}
@Software:   PyCharm
@File    :   fengliu_python_test.py
@Time    :   2018/12/16 16:34
@Desc    :
"""

import pymysql
import requests
from lxml import etree

def get_the_data():
    base_url = 'https://gitee.com/sicefengliu/2018_stage_operation_layout/blob/master/backend/1.md'

    try:
        trial_req = requests.get(base_url)
    except requests.exceptions.HTTPError:
        print("the request is refused")

    trial_data = trial_req.text
    selector = etree.HTML(trial_data)
    the_title = selector.xpath('/html/head/title/text()')
    return trial_data, the_title

def write_into_txt(the_datas):
    outfile = open("the_datas.txt", 'w', encoding='utf-8')
    outfile.writelines(the_datas)
    outfile.close()

def write_into_mysql(title):
    # 插入操作
    db = pymysql.connect(host="localhost", user="root", password="123456", db="fengliu", port=3306)

    # 使用cursor()方法获取操作游标
    cur = db.cursor()

    sql_insert = """insert into fengliu(id,title) values({0},"{1}");""".format(3, title)
    try:
        cur.execute(sql_insert)
        db.commit()
    except Exception as e:
        db.rollback()
    db.close()

if __name__ == '__main__':
    # 通过爬虫获得数据
    print("start get the datas")
    the_datas, title = get_the_data()
    print(the_datas, title)
    print("success!!!")

    # 存储数据
    print("start save the datas")
    write_into_txt(the_datas)
    write_into_mysql(title)
    print("success!!!")

    print("end_of_the_spyder!")
